{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocess Data\n",
    "- Read price and fundamental data and save as dataframe\n",
    "- Clean and process data\n",
    "- Combine these two dataframe\n",
    "- Calculate percent difference\n",
    "- Export data into Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 600188.SH 兖州煤业\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from pandas import Series,DataFrame \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get price data from Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_price = pd.read_excel('price_data.xlsx')\n",
    "\n",
    "# set date as index\n",
    "def setting_index_price(df):\n",
    "    \"\"\"\n",
    "    Returns a sorted datetime index with monthly frequency\n",
    "    \"\"\"\n",
    "    df['date'] = pd.to_datetime(df['date'])\n",
    "    df.set_index(\"date\", inplace=True)\n",
    "    df['date'] = df.index.to_period('M')\n",
    "    df.drop_duplicates('date', keep='last', inplace=True)\n",
    "    df.set_index(\"date\", inplace=True)\n",
    "    # deal with NaN data\n",
    "    df = df.fillna(method='ffill')\n",
    "    # get specific columns we need\n",
    "    df = df.loc[:, ['high', 'low']]\n",
    "    df.sort_index(ascending=True, inplace=True)\n",
    "    return df\n",
    "\n",
    "df_price = setting_index_price(df_price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-02</th>\n",
       "      <td>7.79</td>\n",
       "      <td>7.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-03</th>\n",
       "      <td>10.52</td>\n",
       "      <td>9.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-04</th>\n",
       "      <td>10.08</td>\n",
       "      <td>9.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-05</th>\n",
       "      <td>9.79</td>\n",
       "      <td>9.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-06</th>\n",
       "      <td>10.24</td>\n",
       "      <td>9.88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          high   low\n",
       "date                \n",
       "2000-02   7.79  7.40\n",
       "2000-03  10.52  9.90\n",
       "2000-04  10.08  9.69\n",
       "2000-05   9.79  9.31\n",
       "2000-06  10.24  9.88"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get fundamental data from Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_funda = pd.read_excel('fundamental_data.xlsx')\n",
    "\n",
    "# set date as index\n",
    "def setting_index_fundamental(df):\n",
    "    \"\"\"\n",
    "    Returns a sorted datetime index with month frequency\n",
    "    \"\"\"\n",
    "    df.set_index(\"catalogue\", inplace=True)\n",
    "    df = df.T\n",
    "    df['date'] = pd.to_datetime(df.index)\n",
    "    df.set_index(\"date\", inplace=True)\n",
    "    df['date'] = df.index.to_period('M')\n",
    "    df.drop_duplicates('date', keep='last', inplace=True)\n",
    "    df.set_index(\"date\", inplace=True)\n",
    "    # deal with NaN data\n",
    "    df = df.fillna(method='ffill')\n",
    "    df.sort_index(ascending=True, inplace=True)\n",
    "    return df\n",
    "\n",
    "df_funda = setting_index_fundamental(df_funda)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>catalogue</th>\n",
       "      <th>营业总收入</th>\n",
       "      <th>营业收入</th>\n",
       "      <th>营业总成本</th>\n",
       "      <th>销售费用</th>\n",
       "      <th>管理费用</th>\n",
       "      <th>财务费用</th>\n",
       "      <th>投资净收益</th>\n",
       "      <th>营业利润</th>\n",
       "      <th>净利润</th>\n",
       "      <th>归属于母公司所有者的净利润</th>\n",
       "      <th>基本每股收益</th>\n",
       "      <th>总股本（万股）</th>\n",
       "      <th>总资产</th>\n",
       "      <th>流动资产</th>\n",
       "      <th>总负债</th>\n",
       "      <th>流动负债</th>\n",
       "      <th>少数股东权益</th>\n",
       "      <th>无形资产</th>\n",
       "      <th>商誉</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2002-12</th>\n",
       "      <td>2112123.997</td>\n",
       "      <td>2112123.997</td>\n",
       "      <td>1874723.715</td>\n",
       "      <td>398812.830</td>\n",
       "      <td>390322.766</td>\n",
       "      <td>5068.280</td>\n",
       "      <td>3687.968</td>\n",
       "      <td>246642.076</td>\n",
       "      <td>160758.936</td>\n",
       "      <td>160515.251</td>\n",
       "      <td>0.0559</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.298355e+07</td>\n",
       "      <td>3897273.730</td>\n",
       "      <td>3320038.891</td>\n",
       "      <td>1944259.784</td>\n",
       "      <td>4936.449</td>\n",
       "      <td>777436.629</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-03</th>\n",
       "      <td>2007673.447</td>\n",
       "      <td>2007673.447</td>\n",
       "      <td>1615384.114</td>\n",
       "      <td>375832.455</td>\n",
       "      <td>257389.695</td>\n",
       "      <td>16095.229</td>\n",
       "      <td>0.000</td>\n",
       "      <td>403986.871</td>\n",
       "      <td>259240.516</td>\n",
       "      <td>259213.850</td>\n",
       "      <td>0.0903</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.332590e+07</td>\n",
       "      <td>4427880.597</td>\n",
       "      <td>3339050.702</td>\n",
       "      <td>1920060.964</td>\n",
       "      <td>4367.202</td>\n",
       "      <td>771485.451</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-06</th>\n",
       "      <td>2420206.371</td>\n",
       "      <td>2420206.371</td>\n",
       "      <td>1969433.658</td>\n",
       "      <td>528020.587</td>\n",
       "      <td>341853.476</td>\n",
       "      <td>12027.327</td>\n",
       "      <td>1478.333</td>\n",
       "      <td>473833.772</td>\n",
       "      <td>318358.349</td>\n",
       "      <td>317556.661</td>\n",
       "      <td>0.1106</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.282320e+07</td>\n",
       "      <td>4032762.607</td>\n",
       "      <td>2452823.821</td>\n",
       "      <td>1618354.131</td>\n",
       "      <td>3999.263</td>\n",
       "      <td>802999.924</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-09</th>\n",
       "      <td>2156975.818</td>\n",
       "      <td>2156975.818</td>\n",
       "      <td>1733074.761</td>\n",
       "      <td>389842.771</td>\n",
       "      <td>246555.089</td>\n",
       "      <td>6950.704</td>\n",
       "      <td>5998.113</td>\n",
       "      <td>432243.915</td>\n",
       "      <td>281770.268</td>\n",
       "      <td>281746.701</td>\n",
       "      <td>0.0982</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.299395e+07</td>\n",
       "      <td>4015167.564</td>\n",
       "      <td>2278985.517</td>\n",
       "      <td>1428195.124</td>\n",
       "      <td>4022.830</td>\n",
       "      <td>795830.858</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-12</th>\n",
       "      <td>2080376.568</td>\n",
       "      <td>2080376.568</td>\n",
       "      <td>1709880.282</td>\n",
       "      <td>365378.978</td>\n",
       "      <td>342814.945</td>\n",
       "      <td>6297.159</td>\n",
       "      <td>2940.525</td>\n",
       "      <td>374834.025</td>\n",
       "      <td>246517.586</td>\n",
       "      <td>246847.435</td>\n",
       "      <td>0.0860</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.393860e+07</td>\n",
       "      <td>4490526.096</td>\n",
       "      <td>2911247.621</td>\n",
       "      <td>2346738.239</td>\n",
       "      <td>3739.101</td>\n",
       "      <td>788638.696</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "catalogue        营业总收入         营业收入        营业总成本        销售费用        管理费用  \\\n",
       "date                                                                       \n",
       "2002-12    2112123.997  2112123.997  1874723.715  398812.830  390322.766   \n",
       "2003-03    2007673.447  2007673.447  1615384.114  375832.455  257389.695   \n",
       "2003-06    2420206.371  2420206.371  1969433.658  528020.587  341853.476   \n",
       "2003-09    2156975.818  2156975.818  1733074.761  389842.771  246555.089   \n",
       "2003-12    2080376.568  2080376.568  1709880.282  365378.978  342814.945   \n",
       "\n",
       "catalogue       财务费用     投资净收益        营业利润         净利润  归属于母公司所有者的净利润  基本每股收益  \\\n",
       "date                                                                            \n",
       "2002-12     5068.280  3687.968  246642.076  160758.936     160515.251  0.0559   \n",
       "2003-03    16095.229     0.000  403986.871  259240.516     259213.850  0.0903   \n",
       "2003-06    12027.327  1478.333  473833.772  318358.349     317556.661  0.1106   \n",
       "2003-09     6950.704  5998.113  432243.915  281770.268     281746.701  0.0982   \n",
       "2003-12     6297.159  2940.525  374834.025  246517.586     246847.435  0.0860   \n",
       "\n",
       "catalogue    总股本（万股）           总资产         流动资产          总负债         流动负债  \\\n",
       "date                                                                        \n",
       "2002-12    2870000.0  1.298355e+07  3897273.730  3320038.891  1944259.784   \n",
       "2003-03    2870000.0  1.332590e+07  4427880.597  3339050.702  1920060.964   \n",
       "2003-06    2870000.0  1.282320e+07  4032762.607  2452823.821  1618354.131   \n",
       "2003-09    2870000.0  1.299395e+07  4015167.564  2278985.517  1428195.124   \n",
       "2003-12    2870000.0  1.393860e+07  4490526.096  2911247.621  2346738.239   \n",
       "\n",
       "catalogue    少数股东权益        无形资产   商誉  \n",
       "date                                  \n",
       "2002-12    4936.449  777436.629  0.0  \n",
       "2003-03    4367.202  771485.451  0.0  \n",
       "2003-06    3999.263  802999.924  0.0  \n",
       "2003-09    4022.830  795830.858  0.0  \n",
       "2003-12    3739.101  788638.696  0.0  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_funda.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine price and fundamental dataframe into a joined dataframe\n",
    "Converting all values to numeric values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gross revenue</th>\n",
       "      <th>revenue</th>\n",
       "      <th>total operating cost</th>\n",
       "      <th>selling expense</th>\n",
       "      <th>administration expense</th>\n",
       "      <th>financial expense</th>\n",
       "      <th>net investment income</th>\n",
       "      <th>operating profit</th>\n",
       "      <th>retained profit</th>\n",
       "      <th>net income attributable to parent company</th>\n",
       "      <th>...</th>\n",
       "      <th>total share (10,000 shares)</th>\n",
       "      <th>total assets</th>\n",
       "      <th>current assets</th>\n",
       "      <th>total liabilities</th>\n",
       "      <th>current liabilities</th>\n",
       "      <th>minority equity</th>\n",
       "      <th>intangible assets</th>\n",
       "      <th>goodwill</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2002-12</th>\n",
       "      <td>2112123.997</td>\n",
       "      <td>2112123.997</td>\n",
       "      <td>1874723.715</td>\n",
       "      <td>398812.830</td>\n",
       "      <td>390322.766</td>\n",
       "      <td>5068.280</td>\n",
       "      <td>3687.968</td>\n",
       "      <td>246642.076</td>\n",
       "      <td>160758.936</td>\n",
       "      <td>160515.251</td>\n",
       "      <td>...</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.298355e+07</td>\n",
       "      <td>3897273.730</td>\n",
       "      <td>3320038.891</td>\n",
       "      <td>1944259.784</td>\n",
       "      <td>4936.449</td>\n",
       "      <td>777436.629</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.30</td>\n",
       "      <td>8.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-03</th>\n",
       "      <td>2007673.447</td>\n",
       "      <td>2007673.447</td>\n",
       "      <td>1615384.114</td>\n",
       "      <td>375832.455</td>\n",
       "      <td>257389.695</td>\n",
       "      <td>16095.229</td>\n",
       "      <td>0.000</td>\n",
       "      <td>403986.871</td>\n",
       "      <td>259240.516</td>\n",
       "      <td>259213.850</td>\n",
       "      <td>...</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.332590e+07</td>\n",
       "      <td>4427880.597</td>\n",
       "      <td>3339050.702</td>\n",
       "      <td>1920060.964</td>\n",
       "      <td>4367.202</td>\n",
       "      <td>771485.451</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.15</td>\n",
       "      <td>8.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-06</th>\n",
       "      <td>2420206.371</td>\n",
       "      <td>2420206.371</td>\n",
       "      <td>1969433.658</td>\n",
       "      <td>528020.587</td>\n",
       "      <td>341853.476</td>\n",
       "      <td>12027.327</td>\n",
       "      <td>1478.333</td>\n",
       "      <td>473833.772</td>\n",
       "      <td>318358.349</td>\n",
       "      <td>317556.661</td>\n",
       "      <td>...</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.282320e+07</td>\n",
       "      <td>4032762.607</td>\n",
       "      <td>2452823.821</td>\n",
       "      <td>1618354.131</td>\n",
       "      <td>3999.263</td>\n",
       "      <td>802999.924</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.24</td>\n",
       "      <td>8.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-09</th>\n",
       "      <td>2156975.818</td>\n",
       "      <td>2156975.818</td>\n",
       "      <td>1733074.761</td>\n",
       "      <td>389842.771</td>\n",
       "      <td>246555.089</td>\n",
       "      <td>6950.704</td>\n",
       "      <td>5998.113</td>\n",
       "      <td>432243.915</td>\n",
       "      <td>281770.268</td>\n",
       "      <td>281746.701</td>\n",
       "      <td>...</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.299395e+07</td>\n",
       "      <td>4015167.564</td>\n",
       "      <td>2278985.517</td>\n",
       "      <td>1428195.124</td>\n",
       "      <td>4022.830</td>\n",
       "      <td>795830.858</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.78</td>\n",
       "      <td>7.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-12</th>\n",
       "      <td>2080376.568</td>\n",
       "      <td>2080376.568</td>\n",
       "      <td>1709880.282</td>\n",
       "      <td>365378.978</td>\n",
       "      <td>342814.945</td>\n",
       "      <td>6297.159</td>\n",
       "      <td>2940.525</td>\n",
       "      <td>374834.025</td>\n",
       "      <td>246517.586</td>\n",
       "      <td>246847.435</td>\n",
       "      <td>...</td>\n",
       "      <td>2870000.0</td>\n",
       "      <td>1.393860e+07</td>\n",
       "      <td>4490526.096</td>\n",
       "      <td>2911247.621</td>\n",
       "      <td>2346738.239</td>\n",
       "      <td>3739.101</td>\n",
       "      <td>788638.696</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.23</td>\n",
       "      <td>10.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         gross revenue      revenue  total operating cost  selling expense  \\\n",
       "date                                                                         \n",
       "2002-12    2112123.997  2112123.997           1874723.715       398812.830   \n",
       "2003-03    2007673.447  2007673.447           1615384.114       375832.455   \n",
       "2003-06    2420206.371  2420206.371           1969433.658       528020.587   \n",
       "2003-09    2156975.818  2156975.818           1733074.761       389842.771   \n",
       "2003-12    2080376.568  2080376.568           1709880.282       365378.978   \n",
       "\n",
       "         administration expense  financial expense  net investment income   \\\n",
       "date                                                                         \n",
       "2002-12              390322.766           5068.280                3687.968   \n",
       "2003-03              257389.695          16095.229                   0.000   \n",
       "2003-06              341853.476          12027.327                1478.333   \n",
       "2003-09              246555.089           6950.704                5998.113   \n",
       "2003-12              342814.945           6297.159                2940.525   \n",
       "\n",
       "         operating profit  retained profit  \\\n",
       "date                                         \n",
       "2002-12        246642.076       160758.936   \n",
       "2003-03        403986.871       259240.516   \n",
       "2003-06        473833.772       318358.349   \n",
       "2003-09        432243.915       281770.268   \n",
       "2003-12        374834.025       246517.586   \n",
       "\n",
       "         net income attributable to parent company  ...  \\\n",
       "date                                                ...   \n",
       "2002-12                                 160515.251  ...   \n",
       "2003-03                                 259213.850  ...   \n",
       "2003-06                                 317556.661  ...   \n",
       "2003-09                                 281746.701  ...   \n",
       "2003-12                                 246847.435  ...   \n",
       "\n",
       "         total share (10,000 shares)  total assets  current assets  \\\n",
       "date                                                                 \n",
       "2002-12                    2870000.0  1.298355e+07     3897273.730   \n",
       "2003-03                    2870000.0  1.332590e+07     4427880.597   \n",
       "2003-06                    2870000.0  1.282320e+07     4032762.607   \n",
       "2003-09                    2870000.0  1.299395e+07     4015167.564   \n",
       "2003-12                    2870000.0  1.393860e+07     4490526.096   \n",
       "\n",
       "         total liabilities  current liabilities  minority equity  \\\n",
       "date                                                               \n",
       "2002-12        3320038.891          1944259.784         4936.449   \n",
       "2003-03        3339050.702          1920060.964         4367.202   \n",
       "2003-06        2452823.821          1618354.131         3999.263   \n",
       "2003-09        2278985.517          1428195.124         4022.830   \n",
       "2003-12        2911247.621          2346738.239         3739.101   \n",
       "\n",
       "         intangible assets  goodwill   high    low  \n",
       "date                                                \n",
       "2002-12         777436.629       0.0   8.30   8.01  \n",
       "2003-03         771485.451       0.0   9.15   8.81  \n",
       "2003-06         802999.924       0.0   9.24   8.88  \n",
       "2003-09         795830.858       0.0   7.78   7.67  \n",
       "2003-12         788638.696       0.0  11.23  10.82  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_df = df_funda.join(df_price)\n",
    "joined_df = joined_df.apply(pd.to_numeric)\n",
    "joined_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replace values with percent difference \n",
    "Between each quarter  \n",
    "Mutliply by 100 for better readability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "pcnt_df = joined_df.pct_change(periods=1).apply(lambda x: x*100)\n",
    "pcnt_df = pcnt_df.drop(pcnt_df.index[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Replace infinite values with NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "pcnt_df = pcnt_df.replace([np.inf, -np.inf], np.nan)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the trade strategy\n",
    "- Buy (if the highest high and lowest low of the quarter will both increase by 3% or more in next quarter)\n",
    "- Sell (if the lowest low and highest high of the quarter will both decrease by 3% or more in next quarter)\n",
    "- Hold (if it will not do either in next quarter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "def class_creation(df, thres=3):\n",
    "    \"\"\"\n",
    "    - sell(0)\n",
    "    - buy(1)\n",
    "    - hold(2)\n",
    "    \n",
    "    Threshold can be changed to fit the performance of market and specific stock\n",
    "    \"\"\"\n",
    "    if df['high'] <= -thres and df['low'] <= -thres:\n",
    "        return 0\n",
    "    \n",
    "    elif df['high'] >= thres and df['low'] >= thres:\n",
    "        return 1\n",
    "    \n",
    "    else:\n",
    "        return 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating a new DataFrame that is added the column 'Decision' showing if a quarterly reports indicate us to buy, hold, or sell.  \n",
    "- Why is dataframe shifted by -1?  \n",
    "We should know if the prices will increase/decrease in the next quarter.   \n",
    "In other word, the decision which is indicated by this quarter should be made in last quarter.   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gross revenue</th>\n",
       "      <th>revenue</th>\n",
       "      <th>total operating cost</th>\n",
       "      <th>selling expense</th>\n",
       "      <th>administration expense</th>\n",
       "      <th>financial expense</th>\n",
       "      <th>net investment income</th>\n",
       "      <th>operating profit</th>\n",
       "      <th>retained profit</th>\n",
       "      <th>net income attributable to parent company</th>\n",
       "      <th>...</th>\n",
       "      <th>total assets</th>\n",
       "      <th>current assets</th>\n",
       "      <th>total liabilities</th>\n",
       "      <th>current liabilities</th>\n",
       "      <th>minority equity</th>\n",
       "      <th>intangible assets</th>\n",
       "      <th>goodwill</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>Decision</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003-03</th>\n",
       "      <td>-4.945285</td>\n",
       "      <td>-4.945285</td>\n",
       "      <td>-13.833484</td>\n",
       "      <td>-5.762196</td>\n",
       "      <td>-34.057217</td>\n",
       "      <td>217.567873</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>63.794790</td>\n",
       "      <td>61.260408</td>\n",
       "      <td>61.488611</td>\n",
       "      <td>...</td>\n",
       "      <td>2.636775</td>\n",
       "      <td>13.614822</td>\n",
       "      <td>0.572638</td>\n",
       "      <td>-1.244629</td>\n",
       "      <td>-11.531508</td>\n",
       "      <td>-0.765487</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.240964</td>\n",
       "      <td>9.987516</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-06</th>\n",
       "      <td>20.547810</td>\n",
       "      <td>20.547810</td>\n",
       "      <td>21.917360</td>\n",
       "      <td>40.493611</td>\n",
       "      <td>32.815526</td>\n",
       "      <td>-25.273962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.289399</td>\n",
       "      <td>22.804241</td>\n",
       "      <td>22.507598</td>\n",
       "      <td>...</td>\n",
       "      <td>-3.772301</td>\n",
       "      <td>-8.923411</td>\n",
       "      <td>-26.541283</td>\n",
       "      <td>-15.713399</td>\n",
       "      <td>-8.425051</td>\n",
       "      <td>4.084908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.983607</td>\n",
       "      <td>0.794552</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-09</th>\n",
       "      <td>-10.876368</td>\n",
       "      <td>-10.876368</td>\n",
       "      <td>-12.001364</td>\n",
       "      <td>-26.169021</td>\n",
       "      <td>-27.876969</td>\n",
       "      <td>-42.209071</td>\n",
       "      <td>305.734905</td>\n",
       "      <td>-8.777310</td>\n",
       "      <td>-11.492735</td>\n",
       "      <td>-11.276715</td>\n",
       "      <td>...</td>\n",
       "      <td>1.331521</td>\n",
       "      <td>-0.436302</td>\n",
       "      <td>-7.087272</td>\n",
       "      <td>-11.750148</td>\n",
       "      <td>0.589284</td>\n",
       "      <td>-0.892785</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-15.800866</td>\n",
       "      <td>-13.626126</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-12</th>\n",
       "      <td>-3.551234</td>\n",
       "      <td>-3.551234</td>\n",
       "      <td>-1.338343</td>\n",
       "      <td>-6.275297</td>\n",
       "      <td>39.041926</td>\n",
       "      <td>-9.402573</td>\n",
       "      <td>-50.975832</td>\n",
       "      <td>-13.281827</td>\n",
       "      <td>-12.511143</td>\n",
       "      <td>-12.386752</td>\n",
       "      <td>...</td>\n",
       "      <td>7.269940</td>\n",
       "      <td>11.839071</td>\n",
       "      <td>27.743138</td>\n",
       "      <td>64.314959</td>\n",
       "      <td>-7.052970</td>\n",
       "      <td>-0.903730</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.344473</td>\n",
       "      <td>41.069100</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004-03</th>\n",
       "      <td>12.996168</td>\n",
       "      <td>12.996168</td>\n",
       "      <td>2.929985</td>\n",
       "      <td>-20.751992</td>\n",
       "      <td>9.729352</td>\n",
       "      <td>-29.727009</td>\n",
       "      <td>-57.911291</td>\n",
       "      <td>60.846788</td>\n",
       "      <td>59.022115</td>\n",
       "      <td>58.765778</td>\n",
       "      <td>...</td>\n",
       "      <td>2.888365</td>\n",
       "      <td>14.419261</td>\n",
       "      <td>-1.776355</td>\n",
       "      <td>-2.416558</td>\n",
       "      <td>2.894519</td>\n",
       "      <td>-0.910106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.235085</td>\n",
       "      <td>32.902033</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         gross revenue    revenue  total operating cost  selling expense  \\\n",
       "date                                                                       \n",
       "2003-03      -4.945285  -4.945285            -13.833484        -5.762196   \n",
       "2003-06      20.547810  20.547810             21.917360        40.493611   \n",
       "2003-09     -10.876368 -10.876368            -12.001364       -26.169021   \n",
       "2003-12      -3.551234  -3.551234             -1.338343        -6.275297   \n",
       "2004-03      12.996168  12.996168              2.929985       -20.751992   \n",
       "\n",
       "         administration expense  financial expense  net investment income   \\\n",
       "date                                                                         \n",
       "2003-03              -34.057217         217.567873             -100.000000   \n",
       "2003-06               32.815526         -25.273962                     NaN   \n",
       "2003-09              -27.876969         -42.209071              305.734905   \n",
       "2003-12               39.041926          -9.402573              -50.975832   \n",
       "2004-03                9.729352         -29.727009              -57.911291   \n",
       "\n",
       "         operating profit  retained profit  \\\n",
       "date                                         \n",
       "2003-03         63.794790        61.260408   \n",
       "2003-06         17.289399        22.804241   \n",
       "2003-09         -8.777310       -11.492735   \n",
       "2003-12        -13.281827       -12.511143   \n",
       "2004-03         60.846788        59.022115   \n",
       "\n",
       "         net income attributable to parent company  ...  total assets  \\\n",
       "date                                                ...                 \n",
       "2003-03                                  61.488611  ...      2.636775   \n",
       "2003-06                                  22.507598  ...     -3.772301   \n",
       "2003-09                                 -11.276715  ...      1.331521   \n",
       "2003-12                                 -12.386752  ...      7.269940   \n",
       "2004-03                                  58.765778  ...      2.888365   \n",
       "\n",
       "         current assets  total liabilities  current liabilities  \\\n",
       "date                                                              \n",
       "2003-03       13.614822           0.572638            -1.244629   \n",
       "2003-06       -8.923411         -26.541283           -15.713399   \n",
       "2003-09       -0.436302          -7.087272           -11.750148   \n",
       "2003-12       11.839071          27.743138            64.314959   \n",
       "2004-03       14.419261          -1.776355            -2.416558   \n",
       "\n",
       "         minority equity  intangible assets  goodwill       high        low  \\\n",
       "date                                                                          \n",
       "2003-03       -11.531508          -0.765487       NaN  10.240964   9.987516   \n",
       "2003-06        -8.425051           4.084908       NaN   0.983607   0.794552   \n",
       "2003-09         0.589284          -0.892785       NaN -15.800866 -13.626126   \n",
       "2003-12        -7.052970          -0.903730       NaN  44.344473  41.069100   \n",
       "2004-03         2.894519          -0.910106       NaN  32.235085  32.902033   \n",
       "\n",
       "         Decision  \n",
       "date               \n",
       "2003-03       2.0  \n",
       "2003-06       0.0  \n",
       "2003-09       1.0  \n",
       "2003-12       1.0  \n",
       "2004-03       0.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_df = pcnt_df\n",
    "    \n",
    "decision_df['Decision'] = decision_df.apply(class_creation, axis=1).shift(-1)\n",
    "decision_df = decision_df[0:-1]\n",
    "decision_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Quick check for NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gross revenue                                 0\n",
       "revenue                                       0\n",
       "total operating cost                          0\n",
       "selling expense                               0\n",
       "administration expense                        0\n",
       "financial expense                             0\n",
       "net investment income                         3\n",
       "operating profit                              0\n",
       "retained profit                               0\n",
       "net income attributable to parent company     0\n",
       "basic EPS                                     0\n",
       "total share (10,000 shares)                   0\n",
       "total assets                                  0\n",
       "current assets                                0\n",
       "total liabilities                             0\n",
       "current liabilities                           0\n",
       "minority equity                               0\n",
       "intangible assets                             0\n",
       "goodwill                                     16\n",
       "high                                          0\n",
       "low                                           0\n",
       "Decision                                      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filling the NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "decision_df = decision_df.fillna(method='ffill')\n",
    "# deal with goodwill, replace earliest some goodwill with 0\n",
    "decision_df = decision_df.fillna(value=0)\n",
    "decision_df[np.isinf(decision_df)] = 0\n",
    "final_df = decision_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Quick check for NaN values again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gross revenue                                0\n",
       "revenue                                      0\n",
       "total operating cost                         0\n",
       "selling expense                              0\n",
       "administration expense                       0\n",
       "financial expense                            0\n",
       "net investment income                        0\n",
       "operating profit                             0\n",
       "retained profit                              0\n",
       "net income attributable to parent company    0\n",
       "basic EPS                                    0\n",
       "total share (10,000 shares)                  0\n",
       "total assets                                 0\n",
       "current assets                               0\n",
       "total liabilities                            0\n",
       "current liabilities                          0\n",
       "minority equity                              0\n",
       "intangible assets                            0\n",
       "goodwill                                     0\n",
       "high                                         0\n",
       "low                                          0\n",
       "Decision                                     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.isna().sum()\n",
    "np.isinf(final_df).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Resetting the index\n",
    "We no longer need the dates because we have calculated percent difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gross revenue</th>\n",
       "      <th>revenue</th>\n",
       "      <th>total operating cost</th>\n",
       "      <th>selling expense</th>\n",
       "      <th>administration expense</th>\n",
       "      <th>financial expense</th>\n",
       "      <th>net investment income</th>\n",
       "      <th>operating profit</th>\n",
       "      <th>retained profit</th>\n",
       "      <th>net income attributable to parent company</th>\n",
       "      <th>...</th>\n",
       "      <th>total assets</th>\n",
       "      <th>current assets</th>\n",
       "      <th>total liabilities</th>\n",
       "      <th>current liabilities</th>\n",
       "      <th>minority equity</th>\n",
       "      <th>intangible assets</th>\n",
       "      <th>goodwill</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>Decision</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-4.945285</td>\n",
       "      <td>-4.945285</td>\n",
       "      <td>-13.833484</td>\n",
       "      <td>-5.762196</td>\n",
       "      <td>-34.057217</td>\n",
       "      <td>217.567873</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>63.794790</td>\n",
       "      <td>61.260408</td>\n",
       "      <td>61.488611</td>\n",
       "      <td>...</td>\n",
       "      <td>2.636775</td>\n",
       "      <td>13.614822</td>\n",
       "      <td>0.572638</td>\n",
       "      <td>-1.244629</td>\n",
       "      <td>-11.531508</td>\n",
       "      <td>-0.765487</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.240964</td>\n",
       "      <td>9.987516</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.547810</td>\n",
       "      <td>20.547810</td>\n",
       "      <td>21.917360</td>\n",
       "      <td>40.493611</td>\n",
       "      <td>32.815526</td>\n",
       "      <td>-25.273962</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>17.289399</td>\n",
       "      <td>22.804241</td>\n",
       "      <td>22.507598</td>\n",
       "      <td>...</td>\n",
       "      <td>-3.772301</td>\n",
       "      <td>-8.923411</td>\n",
       "      <td>-26.541283</td>\n",
       "      <td>-15.713399</td>\n",
       "      <td>-8.425051</td>\n",
       "      <td>4.084908</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.983607</td>\n",
       "      <td>0.794552</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-10.876368</td>\n",
       "      <td>-10.876368</td>\n",
       "      <td>-12.001364</td>\n",
       "      <td>-26.169021</td>\n",
       "      <td>-27.876969</td>\n",
       "      <td>-42.209071</td>\n",
       "      <td>305.734905</td>\n",
       "      <td>-8.777310</td>\n",
       "      <td>-11.492735</td>\n",
       "      <td>-11.276715</td>\n",
       "      <td>...</td>\n",
       "      <td>1.331521</td>\n",
       "      <td>-0.436302</td>\n",
       "      <td>-7.087272</td>\n",
       "      <td>-11.750148</td>\n",
       "      <td>0.589284</td>\n",
       "      <td>-0.892785</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-15.800866</td>\n",
       "      <td>-13.626126</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-3.551234</td>\n",
       "      <td>-3.551234</td>\n",
       "      <td>-1.338343</td>\n",
       "      <td>-6.275297</td>\n",
       "      <td>39.041926</td>\n",
       "      <td>-9.402573</td>\n",
       "      <td>-50.975832</td>\n",
       "      <td>-13.281827</td>\n",
       "      <td>-12.511143</td>\n",
       "      <td>-12.386752</td>\n",
       "      <td>...</td>\n",
       "      <td>7.269940</td>\n",
       "      <td>11.839071</td>\n",
       "      <td>27.743138</td>\n",
       "      <td>64.314959</td>\n",
       "      <td>-7.052970</td>\n",
       "      <td>-0.903730</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44.344473</td>\n",
       "      <td>41.069100</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12.996168</td>\n",
       "      <td>12.996168</td>\n",
       "      <td>2.929985</td>\n",
       "      <td>-20.751992</td>\n",
       "      <td>9.729352</td>\n",
       "      <td>-29.727009</td>\n",
       "      <td>-57.911291</td>\n",
       "      <td>60.846788</td>\n",
       "      <td>59.022115</td>\n",
       "      <td>58.765778</td>\n",
       "      <td>...</td>\n",
       "      <td>2.888365</td>\n",
       "      <td>14.419261</td>\n",
       "      <td>-1.776355</td>\n",
       "      <td>-2.416558</td>\n",
       "      <td>2.894519</td>\n",
       "      <td>-0.910106</td>\n",
       "      <td>0.0</td>\n",
       "      <td>32.235085</td>\n",
       "      <td>32.902033</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   gross revenue    revenue  total operating cost  selling expense  \\\n",
       "0      -4.945285  -4.945285            -13.833484        -5.762196   \n",
       "1      20.547810  20.547810             21.917360        40.493611   \n",
       "2     -10.876368 -10.876368            -12.001364       -26.169021   \n",
       "3      -3.551234  -3.551234             -1.338343        -6.275297   \n",
       "4      12.996168  12.996168              2.929985       -20.751992   \n",
       "\n",
       "   administration expense  financial expense  net investment income   \\\n",
       "0              -34.057217         217.567873             -100.000000   \n",
       "1               32.815526         -25.273962             -100.000000   \n",
       "2              -27.876969         -42.209071              305.734905   \n",
       "3               39.041926          -9.402573              -50.975832   \n",
       "4                9.729352         -29.727009              -57.911291   \n",
       "\n",
       "   operating profit  retained profit  \\\n",
       "0         63.794790        61.260408   \n",
       "1         17.289399        22.804241   \n",
       "2         -8.777310       -11.492735   \n",
       "3        -13.281827       -12.511143   \n",
       "4         60.846788        59.022115   \n",
       "\n",
       "   net income attributable to parent company  ...  total assets  \\\n",
       "0                                  61.488611  ...      2.636775   \n",
       "1                                  22.507598  ...     -3.772301   \n",
       "2                                 -11.276715  ...      1.331521   \n",
       "3                                 -12.386752  ...      7.269940   \n",
       "4                                  58.765778  ...      2.888365   \n",
       "\n",
       "   current assets  total liabilities  current liabilities  minority equity  \\\n",
       "0       13.614822           0.572638            -1.244629       -11.531508   \n",
       "1       -8.923411         -26.541283           -15.713399        -8.425051   \n",
       "2       -0.436302          -7.087272           -11.750148         0.589284   \n",
       "3       11.839071          27.743138            64.314959        -7.052970   \n",
       "4       14.419261          -1.776355            -2.416558         2.894519   \n",
       "\n",
       "   intangible assets  goodwill       high        low  Decision  \n",
       "0          -0.765487       0.0  10.240964   9.987516       2.0  \n",
       "1           4.084908       0.0   0.983607   0.794552       0.0  \n",
       "2          -0.892785       0.0 -15.800866 -13.626126       1.0  \n",
       "3          -0.903730       0.0  44.344473  41.069100       1.0  \n",
       "4          -0.910106       0.0  32.235085  32.902033       0.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.reset_index(drop=True, inplace=True)\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop the Price columns\n",
    "- Price high\n",
    "- Price low\n",
    "\n",
    "To prevent any data leakage because we are looking mainly at the relationship of value changes of fundamental data and trade decision rather than prices themselves."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.drop(['high', 'low'], 1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Counting how many classes of trade decision there are\n",
    "Unequal classes are fine because we will use a specific evaluation metric to determine success in classification."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0    29\n",
       "0.0    27\n",
       "2.0    10\n",
       "Name: Decision, dtype: int64"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df['Decision'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export the final dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_excel('final_data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
